package TeachLesson;

import Navigator.student.StudentDao;
import Navigator.student.StudentEnity;

import javax.sql.rowset.serial.SerialStruct;
import javax.swing.text.html.HTMLDocument;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class TeachLessonsDao {
    public Connection getCon(){
        Connection con=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
            con= DriverManager.getConnection(url,"root","root");
        }catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }
    //修改选课
    public int updateStudentLesson(String studentNo,String courseNo){
        Connection con =this.getCon();
        try {
            String sql="update tb_teachlesson set courseNo=? where studentNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,courseNo);
            ps.setString(2,studentNo);
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }

    //查取学生课程



    //添加授课
    public int addTeachLessons(String workerNo,String courseNo){
        Connection con=this.getCon();
        try {
            String sql="insert into tb_teachlesson (workerNo,courseNo)values(?,?)";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ps.setString(2,courseNo);
            //执行
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }
    public ArrayList<techLessonEnity> getTeachDetail(){
        Connection con=this.getCon();
        try {
            String sql="SELECT tb_teachlesson.courseNo,tb_teachlesson.workerNo,tb_worker.workerName,tb_course.courseName FROM tb_course," +
                    "tb_worker,tb_teachlesson WHERE tb_course.courseNo=tb_teachlesson.courseNo AND tb_worker.workerNo=tb_teachlesson.workerNo";
            PreparedStatement ps=con.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            ArrayList<techLessonEnity> tleList=new ArrayList<>();
            while (rs.next()){
                techLessonEnity a=new techLessonEnity();
                a.setCourseNo(rs.getString("courseNo"));
                a.setWorkerNO(rs.getString("workerNo"));
                a.setCourseName(rs.getString("courseName"));
                a.setWorkerName(rs.getString("workerName"));
                tleList.add(a);
            }
            rs.next();
            ps.close();
            con.close();
            return tleList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    public int deleteTeachLesson(String workerNo,String courseNo){
        Connection con=this.getCon();
        try {
            String sql="delete from tb_teachlesson where workerNo=? and courseNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ps.setString(2,courseNo);
            int row=ps.executeUpdate();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }

    public ArrayList<techLessonEnity> getTeachDetail(String workerNo){
        Connection con=this.getCon();
        try {
            String sql="SELECT tb_teachlesson.courseNo,tb_teachlesson.workerNo,tb_worker.workerName,tb_course.courseName FROM tb_course," +
                    "tb_worker,tb_teachlesson WHERE tb_course.courseNo=tb_teachlesson.courseNo AND tb_worker.workerNo=tb_teachlesson.workerNo and +" +
                    "tb_teachlesson.workerNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ResultSet rs=ps.executeQuery();
            ArrayList<techLessonEnity> tleList=new ArrayList<>();
            while (rs.next()){
                techLessonEnity a=new techLessonEnity();
                a.setCourseNo(rs.getString("courseNo"));
                a.setWorkerNO(rs.getString("workerNo"));
                a.setCourseName(rs.getString("courseName"));
                a.setWorkerName(rs.getString("workerName"));
                tleList.add(a);
            }
            rs.next();
            ps.close();
            con.close();
            return tleList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    public int updateIntroduce(String workerNo,String courseNo,String CIntroduce){
         Connection con=this.getCon();
         try {
             String sql="update tb_teachlesson set CIntroduce=? where workerNo=? and courseNo=?";
             PreparedStatement ps=con.prepareStatement(sql);
             ps.setString(1,CIntroduce);
             ps.setString(2,workerNo);
             ps.setString(3,courseNo);
             int row=ps.executeUpdate();
             ps.close();
             con.close();
             return row;
         }catch (Exception e){
             e.printStackTrace();
         }
         return 0;
    }

    //添加单个学生课程
    public int addStudentLesson(String studentNo,String courseNo,String workerNo){
        Connection con=this.getCon();
        try {
            String sql="insert into tb_studentlesson (studentNo,courseNo,workerNo) values(?,?,?)";
            //创建ps对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,studentNo);
            ps.setString(2,courseNo);
            ps.setString(3,workerNo);
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            this.updateStudentLesson(studentNo,courseNo);
            e.printStackTrace();
        }
        return 0;
    }

    //获取课程简介
    public String getIntroduce(String workerNo,String courseNo){
        Connection con=this.getCon();
        try {
            String sql="select CIntroduce from tb_teachlesson where workerNo=? and courseNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ps.setString(2,courseNo);
            ResultSet rs=ps.executeQuery();
            String CIntroduce=null;
            while (rs.next()){
                CIntroduce=rs.getString("CIntroduce");
            }
            ps.close();
            con.close();
            return CIntroduce;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    //按班级添加学生
    public int addStudentByClass(String className,String courseNo,String workerNo){
        StudentDao studentDao=new StudentDao();
        //获取该班级的学生学号
        ArrayList<StudentEnity> studentNolList=studentDao.getStudentNoByClass(className);
        //添加学生
        for (StudentEnity temp:studentNolList){
            this.addStudentLesson(temp.getStudentNo(),courseNo,workerNo);//添加学生
        }
        return 1;
    }


    //修改课程选课
    public int updateTeachLesson(String workerNo,String courseNo,String workerNoNew){
        Connection con=this.getCon();
        //sql
        try {
            String sql1="update tb_teachlesson set workerNo=? where workerNo=? and courseNo=?";
            String sql2="update tb_solvepro set workerNo=? where workerNo=? and courseNo=?";
            String sql3="update tb_studentlesson set workerNo=? where workerNo=? and courseNo=?";

            PreparedStatement ps1=con.prepareStatement(sql1);
            PreparedStatement ps2=con.prepareStatement(sql2);
            PreparedStatement ps3=con.prepareStatement(sql3);

            ps1.setString(1,workerNoNew);
            ps1.setString(2,workerNo);
            ps1.setString(3,courseNo);

            ps2.setString(1,workerNoNew);
            ps2.setString(2,workerNo);
            ps2.setString(3,courseNo);

            ps3.setString(1,workerNoNew);
            ps3.setString(2,workerNo);
            ps3.setString(3,courseNo);

            int row1=ps1.executeUpdate();
            int row2=ps2.executeUpdate();
            int row3=ps3.executeUpdate();
            return ((row1+row2+row3));
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    //查询课程介绍
    public String getCIntroduce(String workerNo,String courseNo){
        Connection con=this.getCon();
        try {
            //sql
            String sql="select CIntroduce from tb_teachlesson where workerNo=? and courseNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ps.setString(2,courseNo);
            //执行
            ResultSet rs=ps.executeQuery();
            String CIntroduce="课程介绍";
            while (rs.next()){
                CIntroduce=rs.getString("CIntroduce");
            }
            rs.close();
            ps.close();
            con.close();
            return CIntroduce;
        }catch (Exception e){
            e.printStackTrace();
            return "课程介绍";
        }
    }


}

